#!/usr/bin/env python
# coding:utf-8


import logging
from datatabletransfer import DataTableTransfer
from copy import deepcopy
from utils.mysqlutils import MysqlUtils
from utils.commontools import convert_to_date
from fieldsmap import *


class FundAssetAllocation(DataTableTransfer):
    """
    基金资产组合(TQ_FD_ASSETPORTFOLIO)/基金资产组合附表(TQ_FD_ASSETPORTFOLIOSUB) --> 基金资产配置表(fund_asset_allocation)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date
         from TQ_FD_ASSETPORTFOLIO ;'''
    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date
        from fund_asset_allocation f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, SECURITYID as SECURITYID1,PUBLISHDATE,REPORTDATE,
    # TOTFDASSET,TOTFDNAV,TOTFDNAVCLA,EQUITYINVEMKTCAP,EQUITYINVERTO,SKMKTCAP,SKRATIO,DRMKTCAP,DRRTO,REITSMKTCAP,REITSRTO,
    # FIXEDRTO,BDMKTCAP,BDRTO,NDMKTCAP,NDRTO,LOCTREBOND,LOCTREBONDRTO,CBBILLMKTCAP,CBBILLRTO,FINBDMKTCAP,FINBDRTO,
    # POLFINBDMKTCAP,POLFINBDRTO,CORPBDMKTCAP,CORPBDRTO,STFINBDMKTCAP,STFINBDRTO,MTNOTEMKTCAP,MTNOTERTO, FIXEDMKTCAP,
    # CONVBDMKTCAP,CONVBDRTO,PRIVBDMKTCAP,PRIVBDRTO,NCD,NCDRTO,OTHBDMKTCAP,OTHBDRTO,ABSMKTCAP,ABSRTO,FDMKTCAP,
    # FDRTO,DERIMKTCAP,DERIRTO,FORWMKTCAP,FORWRTO,FUTMKTCAP,FUTRTO,OPNMKTCAP,OPNRTO,WRTMKTCAP,WRTRTO,BUYBFINASSMKTCAP,
    # BUYBFINASSRTO,OUTRTREPOMKTCAP,OUTRIGHTREPORTO,COMMSPOTMKTCAP,COMMSPOTRTO,GOLDMKTCAP,GOLDRTO,PRECMETALCAP,
    # PRECMETALRTO,CURFDSMKTCAP,CURFDSRTO,DEPOSITMKTCAP,DEPOSITRTO,BKDEPOSITMKTCAP,BKDEPOSITRTO,LIQUPROVMKTCAP,
    # LIQUPROVRTO,OTHINVEMKTCAP,OTHINVERTO,MARGINMKTCAP,MARGINRTO,SECUCLINGMKTCAP,SECUCLINGRTO,DVRECMKTCAP,DVRECRTO,
    # INTERECMKTCAP,INTERECRTO,RECSACCAMKTCAP,RECSACCARTO,OTHRECEMKTCAP,OTHRECERTO,PREPEXPEMKTCAP,PREPEXPERTO,OTHMKTCAP,
    # OTHRTO,SLREPASSEMKTCAP,SLREPASSERTO,CREDITBALMKTCAP,CREDITBALRTO from TQ_FD_ASSETPORTFOLIO limit %s, 1000;''')

    TABLE = 'fund_asset_allocation'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundAssetAllocationMap
    MAP_VALUES = FundAssetAllocationValueMap

    RATIOS = {
        'equ_tot_ratio': '1101',
        'com_tot_ratio': '1102',
        'dep_tot_ratio': '1104',
        'pre_tot_ratio': '1103',
        'est_tot_ratio': '1105',
        'fund_tot_ratio': '1201',
        'fixed_tot_ratio': '1301',
        'bond_tot_ratio': '1302',
        'abs_tot_ratio': '1303',
        'deri_tot_ratio': '1401',
        'for_tot_ratio': '1402',
        'fut_tot_ratio': '1403',
        'opt_tot_ratio': '1404',
        'war_tot_ratio': '1405',
        'buy_back_asset_tot_ratio': '1501',
        'outright_tot_ratio': '1502',
        'cur_tot_ratio': '1601',
        'deposit_tot_ratio': '1701',
        'other_asset_tot_ratio': '1801',
    }

    @classmethod
    def upset(cls, table, row, column_value, upset):
        """
        更新记录: 各个指标占总资产比例
        :param table:
        :param row:
        :param column_value:
        :param upset:
        :return:
        """

        item = deepcopy(cls.MAP_FIELDS)
        for key, value in item.iteritems():
            # 判断是否存在值匹配
            if value and cls.MAP_VALUES.has_key(key):
                map_value = cls.MAP_VALUES[key]
                if map_value.keys()[0] == 'SQL':   # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    item[key] = cls.exec_sql(conf, query, (row[value], ), default_value)
                else:
                    item[key] = map_value.get(row[value], 0)
            # 如果value存在，进行匹配
            elif value:
                item[key] = row[value] if row[value] or row[value] == 0 else None

        # 修正默认值
        for key, value in cls.DEFAULT_VALUE.iteritems():
            if not item[key]:
                item[key] = value

        # 修正日期时间型
        for key, value in cls.DATETIME.iteritems():
            if item.has_key(key) and len(str(item[key])) == 8 and item[key] != '19000101':
                item[key] = convert_to_date('{0}-{1}-{2} 00:00:00'
                                            .format(item[key][:4], item[key][4:6], item[key][6:8]))
            else:
                item[key] = None

        # 各个指标占总资产比例
        ratios = cls.get_ratios(row['SECURITYID1'], row['REPORTDATE'])
        if ratios:
            for k, v in cls.RATIOS.iteritems():
                item[k] = ratios.get(v)

        cls.write_mysql(table, item, column_value, upset)

    @classmethod
    def get_ratios(cls, security_id, report_date):
        """
        获取各个指标占总资产比例
        :param security_id:
        :param report_date:
        :return:
        """

        query = ('''select PROJECTCODE, TOTFDASSETRTO from TQ_FD_ASSETPORTFOLIOSUB where SECURITYID=%s 
        and REPORTDATE=%s 
        and PROJECTCODE in (1101,1102,1103,1104,1105,1301,1302,1303,1201,1401,1402,1403,10404,
        1405,1501,1502,1601,1701,1801);''')
        conn = MysqlUtils.connect(conf=cls.funds)
        cursors = MysqlUtils.common_query(conn=conn, query=query, args=(security_id, report_date))
        if cursors and cursors.rowcount:
            all_rows = cursors.fetchall()
            rows = dict(all_rows)
        else:
            rows = None
            # logging.warn('The record no data. SECURITYID:{0}, REPORTDATE:{1}'.format(security_id, report_date))
        return rows


class FundInvestIndustry(DataTableTransfer):
    """
    基金投资组合(按行业)(TQ_FD_INVPORTFOLIO) --> 基金投资行业配置(fund_invest_industry)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,INDUSTRYCODE as indu_published_code
    from TQ_FD_INVPORTFOLIO ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,indu_published_code
     from fund_invest_industry f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''

    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,ENDDATE,INDUSTRYTYPE,INDUSTRYCODE,
    # INDUSTRYNAME,MKTCAP,ACCNETMKTCAP,M_ACTINVEST,A_ACTINVEST,M_INDEXINVEST,A_INDEXINVEST
    # from TQ_FD_INVPORTFOLIO limit %s, 1000;''')

    TABLE = 'fund_invest_industry'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'indu_published_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundInvestIndustryMap
    MAP_VALUES = FundInvestIndustryValueMap


class FundHshkIndustry(DataTableTransfer):
    """
    沪深港通行业投资组合(TQ_FD_HSHKIPORT) --> 沪深港通基金行业配置(fund_hshk_industry)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date,INDUSTRYCODE as indu_published_code
       from TQ_FD_HSHKIPORT a inner join TQ_OA_STCODE b on a.SECODE=b.SECODE ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,indu_published_code
        from fund_hshk_industry f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', b.SECURITYID) as SECURITYID, a.DECLAREDATE, a.REPORTDATE,
    # a.INDCLASSCODE, a.INDUSTRYCODE,a.INDUSTRYNAME,
    # case when a.INVESTTYPE=1 then a.MVALUE else null end as active_inv_value,
    # case when a.INVESTTYPE=2 then a.MVALUE else null end as index_inv_value,
    # case when a.INVESTTYPE=1 then a.ACCNETMKTCAP else null end as active_inv_net_ratio,
    # case when a.INVESTTYPE=2 then a.ACCNETMKTCAP else null end as index_inv_net_ratio,
    # case when a.INVESTTYPE=1 then a.MVALUE else 0 end+case when a.INVESTTYPE=2 then a.MVALUE else 0 end as indu_value,
    # case when a.INVESTTYPE=1 then a.ACCNETMKTCAP else 0 end+case when a.INVESTTYPE=2 then a.ACCNETMKTCAP else 0 end
    # as indu_value_net_ratio from TQ_FD_HSHKIPORT a inner join TQ_OA_STCODE b on a.SECODE=b.SECODE limit %s, 1000;''')

    TABLE = 'fund_hshk_industry'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'indu_published_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundHshkIndustryMap
    MAP_VALUES = FundHshkIndustryValueMap
    IDX = ('sec_uni_code',)

    @classmethod
    def upset_mysql(cls, table, rows):
        """
        导入数据至目标库(中心库)
        (1)来源source_id是否存在 ==> 存在：update; 不存在： 转(2)
        (2)名称关联是否匹配存在： ==> 存在：update(核算合计值)
        'index_inv_value', 'index_inv_net_ratio',
        'active_inv_value','active_inv_net_ratio'
        'indu_value', 'indu_value_net_ratio'
        :param table:
        :param rows:
        :return:
        """

        for index, row in enumerate(rows):
            record = cls.find_record(table, row, cls.SOURCE_ID) if cls.SOURCE_ID else None
            if record:
                # 修正 index_inv_value,index_inv_net_ratio,active_inv_value,active_inv_net_ratio,
                # indu_value,indu_value_net_ratio的值
                if not row['index_inv_value'] and record['index_inv_value']:
                    row['index_inv_value'] = record['index_inv_value']
                if not row['index_inv_net_ratio'] and record['index_inv_net_ratio']:
                    row['index_inv_net_ratio'] = record['index_inv_net_ratio']
                if not row['active_inv_value'] and record['active_inv_value']:
                    row['active_inv_value'] = record['active_inv_value']
                if not row['active_inv_net_ratio'] and record['active_inv_net_ratio']:
                    row['active_inv_net_ratio'] = record['active_inv_net_ratio']
                index_value = row['index_inv_value'] if row['index_inv_value'] else 0
                active_inv_value = row['active_inv_value'] if row['active_inv_value'] else 0
                index_inv_net_ratio = row['index_inv_net_ratio'] if row['index_inv_net_ratio'] else 0
                active_inv_net_ratio = row['active_inv_net_ratio'] if row['active_inv_net_ratio'] else 0
                row['indu_value'] = index_value + active_inv_value if index_value + active_inv_value > 0 else None
                row['indu_value_net_ratio'] = index_inv_net_ratio + active_inv_net_ratio\
                    if index_inv_net_ratio+active_inv_net_ratio > 0 else None
                cls.upset(table=table, row=row, column_value=record[cls.COLUMN_ID], upset=True)
            else:
                cls.upset(table=table, row=row, column_value=None, upset=False)

    @classmethod
    def find_record(cls, table, row, columns=tuple()):
        """
        查找记录
        :param table:
        :param row:
        :param columns:
        :return:
        """

        conditions = ''
        values = list()
        for index, column in enumerate(columns):
            if column in cls.MAP_VALUES.keys():
                map_value = cls.MAP_VALUES[column]
                if map_value.keys()[0] == 'SQL':  # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    value = cls.exec_sql(conf, query, (row[cls.MAP_FIELDS[column]],), default_value)
                else:
                    value = map_value.get(row[cls.MAP_FIELDS[column]], None)
            else:  # 直接取值
                value = row[cls.MAP_FIELDS[column]]

            # 查询走索引，提高检索速度
            if column in cls.IDX:
                conditions += ''' and {0}=%s'''.format(column)
            else:
                conditions += ''' and ifnull({0},'')=%s'''.format(column)

            # 排空和‘19000101’
            if value is None or value == '19000101':
                value = ''
            # 修正日期时间类型
            if cls.DATETIME.has_key(column) and value:
                value = '{0}-{1}-{2}'.format(value[:4], value[4:6], value[6:])
            values.append(value)

        conn = MysqlUtils.connect(cls.data_center)
        query_sql = ('''select id,active_inv_value, active_inv_net_ratio, index_inv_value, index_inv_net_ratio, 
        indu_value, indu_value_net_ratio  from {0} where{1};'''
                     .format(table, conditions[4:]))
        cursor = MysqlUtils.common_query(conn=conn, query=query_sql, args=tuple(values))
        if cursor and cursor.rowcount:
            row = MysqlUtils.sql_row_to_dict(cursor, cursor.fetchone())
            cursor.close()
        else:
            row = None
        conn.close()
        return row


class FundInvestDetailStock(DataTableTransfer):
    """
    基金投资股票明细(TQ_FD_SKDETAIL) --> 基金投资明细(fund_invest_detail)
    """
    QUERY_SOURCE = '''select  a.SECURITYID as sec_uni_code,a.ENDDATE as end_date,b.SYMBOL as inv_sec_uni_code
    from TQ_FD_SKDETAIL a inner join TQ_OA_STCODE b on a.SKCODE=b.SECODE and b.ISVALID=1  and b.SETYPE in(101,105)
    union all select  SECURITYID as sec_uni_code,ENDDATE as end_date, BONDCODE as inv_sec_uni_code  from TQ_FD_BDDETAIL
     union all select SECURITYID sec_uni_code, ENDDATE as end_date,  FUNDCODE as inv_sec_uni_code from TQ_FD_FDDETAIL;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,
    replace(c.sec_code,'FD','') as inv_sec_uni_code from fund_invest_detail f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code
    join sec_basic_info c on f.inv_sec_uni_code = c.sec_uni_code where  f.status<>'9' and c.sec_type=1004001 and c.sec_small_type=1004001001 union ALL
    select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,
    replace(c.source_id,'FD','') as inv_sec_uni_code from fund_invest_detail f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code
    join sec_basic_info c on f.inv_sec_uni_code = c.sec_uni_code where c.sec_type in(1004002,1004003) and  f.status<>'9'  ;'''

    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', a.SECURITYID) as SECURITYID,a.PUBLISHDATE,a.ENDDATE,b.SYMBOL,a.SKNAME,
    # case when b.SETYPE=101 then 1004001001 when b.SETYPE=105 then 1004017 end  as inv_sec_type,
    # case when a.INVESTTYPE=1 then a.HOLDAMT else null end as active_inv_amount,
    # case when a.INVESTTYPE=2 then a.HOLDAMT else null end as index_inv_amount,
    # case when a.INVESTTYPE=1 then a.HOLDMKTCAP else null end as active_inv_value,
    # case when a.INVESTTYPE=2 then a.HOLDMKTCAP else null end as index_inv_value,
    # case when a.INVESTTYPE=1 then a.NAVRTO else null end as active_inv_net_ratio,
    # case when a.INVESTTYPE=2 then a.NAVRTO else null end as index_inv_net_ratio,
    # case when a.INVESTTYPE=1 then a.HOLDAMT else 0 end+case when a.INVESTTYPE=2 then a.HOLDAMT else 0 end
    # as hold_sec_amount, case when a.INVESTTYPE=1 then a.HOLDMKTCAP else 0 end+case when a.INVESTTYPE=2
    # then a.HOLDMKTCAP else 0 end as hold_sec_value, case when a.INVESTTYPE=1 then a.NAVRTO else 0 end+case
    # when a.INVESTTYPE=2 then a.NAVRTO else 0 end as sec_value_net_ratio
    # from TQ_FD_SKDETAIL a inner join TQ_OA_STCODE b on a.SKCODE=b.SECODE and b.ISVALID=1
    # and b.SETYPE in(101,105) limit %s, 1000;''')

    TABLE = 'fund_invest_detail'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'inv_sec_uni_code':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundInvestStockMap
    MAP_VALUES = FundInvestStockValueMap

    @classmethod
    def upset(cls, table, row, column_value, upset):
        """
        更新记录(insert or update)
        :param table:
        :param row:
        :param column_value:
        :param upset:
        :return:
        """

        item = deepcopy(cls.MAP_FIELDS)
        for key, value in item.iteritems():
            # 判断是否存在值匹配
            if value and cls.MAP_VALUES.has_key(key):
                map_value = cls.MAP_VALUES[key]
                if map_value.keys()[0] == 'SQL':   # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]

                    # 对于港股，需要根据sec_code+sec_name进行匹配
                    if row['inv_sec_type'] == 1004017 and key == 'inv_sec_uni_code':
                        map_value = cls.MAP_VALUES['inv_sec_uni_code_hk']
                        conf = map_value.values()[0][0]
                        query = map_value.values()[0][1]
                        default_value = map_value.values()[0][2]
                        item[key] = cls.exec_sql(conf, query, (row['SYMBOL'], row['SKNAME']), default_value)
                    else:
                        item[key] = cls.exec_sql(conf, query, (row[value], ), default_value)
                else:
                    item[key] = map_value.get(row[value], 0)
            # 如果value存在，进行匹配
            elif value:
                item[key] = row[value] if row[value] or row[value] == 0 else None

        # 修正默认值
        for key, value in cls.DEFAULT_VALUE.iteritems():
            if not item[key]:
                item[key] = value

        # 修正日期时间型
        for key, value in cls.DATETIME.iteritems():
            if item.has_key(key) and len(str(item[key])) == 8 and item[key] != '19000101':
                item[key] = convert_to_date('{0}-{1}-{2} 00:00:00'
                                            .format(item[key][:4], item[key][4:6], item[key][6:8]))
            else:
                item[key] = None

        cls.write_mysql(table, item, column_value, upset)


class FundInvestDetailBonds(DataTableTransfer):
    """
    基金投资债券明细(TQ_FD_BDDETAIL) --> 基金投资明细(fund_invest_detail)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID, PUBLISHDATE, ENDDATE, 
    concat('FD', BONDCODE) as BONDCODE, BONDNAME, 
    HOLDAMT, HOLDMKTCAP, NAVRTO from TQ_FD_BDDETAIL limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) as SECURITYID, PUBLISHDATE, ENDDATE, 
    concat('FD', BONDCODE) as BONDCODE, BONDNAME, 
    HOLDAMT, HOLDMKTCAP, NAVRTO from TQ_FD_BDDETAIL 
    where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_BDDETAIL;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_BDDETAIL  
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL

    TABLE = 'fund_invest_detail'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'end_date', 'inv_sec_uni_code')   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundInvestBondsMap
    MAP_VALUES = FundInvestBondsValueMap


class FundInvestDetailFunds(DataTableTransfer):
    """
    基金投资基金明细(TQ_FD_FDDETAIL) --> 基金投资明细(fund_invest_detail)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID, PUBLISHDATE, ENDDATE, 
    concat('FD', FUNDCODE) as FUNDCODE, FUNDNAME, HOLDAMT, HOLDMKTCAP, NAVRTO from TQ_FD_FDDETAIL limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) as SECURITYID, PUBLISHDATE, ENDDATE, 
    concat('FD', FUNDCODE) as FUNDCODE, FUNDNAME, HOLDAMT, HOLDMKTCAP, NAVRTO from TQ_FD_FDDETAIL 
    where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_FDDETAIL;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_FDDETAIL 
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL

    TABLE = 'fund_invest_detail'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'end_date', 'inv_sec_uni_code')   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundInvestFundMap
    MAP_VALUES = FundInvestFundValueMap


class FundPortfolioChangeQDII(DataTableTransfer):
    """
    QDII股票投资组合重大变化(TQ_FD_QDIISKPCHG) --> 基金投资组合重大变动(fund_portfolio_change)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date,NULL as inv_sec_uni_code,ESYMBOL as inv_sec_code,
    case when CHANGEPATH=1 then '累计买入' when CHANGEPATH=2 then '累计卖出' else '' end as change_direction
    from TQ_FD_QDIISKPCHG UNION ALL 
    select  SECURITYID as sec_uni_code,REPORTDATE as end_date,SYMBOL as inv_sec_uni_code,SYMBOL as inv_sec_code, 
    case when CHGDIRECTION=1 then '累计买入' when CHGDIRECTION=2 then '累计卖出' else '' end as change_direction
    from TQ_FD_STCHG ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,
    replace(c.sec_code,'FD','') as inv_sec_uni_code, inv_sec_code,change_direction 
    from fund_portfolio_change f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code JOIN sec_basic_info c 
     on f.inv_sec_uni_code = c.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, DECLAREDATE, REPORTDATE, ESYMBOL, COMPANYNAME,
    # STOCKRANK, case when CHANGEPATH=1 then '累计买入' when CHANGEPATH=2 then '累计卖出' else '' end as CHANGEPATH,
    # CHANGEAMT, NAVRATIO from TQ_FD_QDIISKPCHG limit %s, 1000;''')

    TABLE = 'fund_portfolio_change'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False ,'inv_sec_uni_code':False, 'inv_sec_code':False, 'change_direction':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundPortfolioChangeQDIIMap
    MAP_VALUES = FundPortfolioChangeQDIIValueMap


class FundPortfolioChange(DataTableTransfer):
    """
    股票组合重大变化(TQ_FD_STCHG) --> 基金投资组合重大变动(fund_portfolio_change)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, PUBLISHDATE, REPORTDATE, SYMBOL, SKNAME, RANK, 
    case when CHGDIRECTION=1 then '累计买入' when CHGDIRECTION=2 then '累计卖出' else '' end as CHGDIRECTION,
    CHGAMT, ININAVRTO from TQ_FD_STCHG limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) SECURITYID, PUBLISHDATE, REPORTDATE, SYMBOL,
    SKNAME, RANK, case when CHGDIRECTION=1 then '累计买入' when CHGDIRECTION=2 then '累计卖出' else '' end as CHGDIRECTION,
    CHGAMT, ININAVRTO from TQ_FD_STCHG
    where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_STCHG;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_STCHG 
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL

    TABLE = 'fund_portfolio_change'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'end_date', 'inv_sec_uni_code', 'inv_sec_code', 'change_direction')
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundPortfolioChangeMap
    MAP_VALUES = FundPortfolioChangeValueMap


class QDIIFundNationAllocation(DataTableTransfer):
    """
    股票投资组合(按国家&地区)(TQ_FD_QDIISKP) --> QDII投资区域配置(fund_qdii_nation_allocation)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date,COUNTRYCODE as country_code
    from TQ_FD_QDIISKP ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,country_code
        from fund_qdii_nation_allocation f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, DECLAREDATE, REPORTDATE, COUNTRYCODE,
    # replace(COUNTRY, '伦敦','英国') as COUNTRY, SKMARKETV, NAVRATIO from TQ_FD_QDIISKP limit %s, 1000;''')

    TABLE = 'fund_qdii_nation_allocation'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'country_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = QDIIFundNationAllocationMap
    MAP_VALUES = QDIIFundNationAllocationValueMap


class QDIIFundInvestIndustry(DataTableTransfer):
    """
    QDII行业投资明细表(TQ_FD_QDIIPORT)	--> QDII投资行业配置(fund_qdii_invest_industry)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date,INDCLASS as indu_standard,
    replace(INDUSTRYCODE, 'ZZZ', 'TOT') as indu_published_code from TQ_FD_QDIIPORT  ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,indu_standard,
     indu_published_code  from fund_qdii_invest_industry f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, DECLAREDATE, REPORTDATE, INDCLASS,
    # replace(INDUSTRYCODE, 'ZZZ', 'TOT') as INDUSTRYCODE, INDUSTRYNAME, MARKETV, PROPORTION from TQ_FD_QDIIPORT
    # limit %s, 1000;''')

    TABLE = 'fund_qdii_invest_industry'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'indu_standard':True, 'indu_published_code':False} # 被关联表的主键, 源表与目标表的关联字段

    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = QDIIFundInvestIndustryMap
    MAP_VALUES = QDIIFundInvestIndustryValueMap


class QDIIFundRatedBond(DataTableTransfer):
    """
    QDII期末按债券信用等级分类的债券投资组合(TQ_FD_QDIIBDCRATING) --> QDII信用等级分类债券投资组合(fund_qdii_rated_bond)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,CREDITRCODE as credit_rating_code
     from TQ_FD_QDIIBDCRATING ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,credit_rating_code
    from fund_qdii_rated_bond f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, DECLAREDATE, ENDDATE, CREDITRCODE, CREDITRCN,
    #  FAIRVALUE, NAVRATIO from TQ_FD_QDIIBDCRATING limit %s, 1000;''')

    TABLE = 'fund_qdii_rated_bond'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'credit_rating_code':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = QDIIFundRatedBondMap
    MAP_VALUES = QDIIFundRatedBondValueMap


class QDIIFundInvestDetail(DataTableTransfer):
    """
    QDII基金投资证券表(TQ_FD_QDIIINVEST) --> QDII基金投资明细(fund_qdii_invest_detail)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,STYPE as inv_sec_type,SYMBOL as inv_sec_code,
     SNAME as  inv_sec_name  from TQ_FD_QDIIINVEST ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(f.end_date,'-','') as end_date,inv_sec_type,
    inv_sec_code, inv_sec_name  from fund_qdii_invest_detail f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code 
    where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, DECLAREDATE, ENDDATE, SYMBOL, SNAME,STYPE, CUR,
    #  HOLDVOL,HOLDVALUE,ACCTFORNAV from TQ_FD_QDIIINVEST limit %s, 1000;''')

    TABLE = 'fund_qdii_invest_detail'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'inv_sec_type':True, 'inv_sec_code':False, 'inv_sec_name':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = QDIIFundInvestDetailMap
    MAP_VALUES = QDIIFundInvestDetailValueMap


if __name__ == '__main__':

    log_path = '../logs/export_funds_assets1_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # FundAssetAllocation.work()
    # FundInvestIndustry.work()
    # FundHshkIndustry.work_increase()
    FundInvestDetailStock.work()
    # FundInvestDetailBonds.work()
    # FundInvestDetailFunds.work()
    # FundPortfolioChangeQDII.work()
    # FundPortfolioChange.work()
    # QDIIFundNationAllocation.work()
    # QDIIFundInvestIndustry.work()
    # QDIIFundRatedBond.work()
    # QDIIFundInvestDetail.work()
